﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class OS : BasicSelfService, IOS
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
        private const string sqlGetAllOS = "select * from os_table";
        private const string sqlDelete = "delete from os_table where id=:id";
        private const string sqlIsExistOS = "select * from os_table where osname=:osName";
        private const string sqlInsert = "insert into os_table (id,osname) values(:ID,:NameID)";
        #region  //Old
        //public OSModel GetOSByID(int ID)
        //{
        //    QueryParameter[] attParam = new QueryParameter[] {
        //                new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
        //                OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
        //            };
        //    OSModel model = new OSModel();
        //    using (OracleConnection conn = new OracleConnection(connstr))
        //    {
        //        conn.Open();
        //        OracleCommand com = conn.CreateCommand();
        //        com.CommandType = CommandType.Text;
        //        com.CommandText = @"select id,osname  from OS_Table where id = :id";
        //        var accountid = new OracleParameter("id", OracleDbType.NVarchar2, 100);
        //        com.Parameters.Add(accountid);

        //        using (var reader = com.ExecuteReader())
        //        {
        //            if (reader.Read())
        //            {
        //                if (reader["ID"] != DBNull.Value) model.ID = (decimal)reader["ID"];
        //                if (reader["osName"] != DBNull.Value) model.Name = (string)reader["osName"];
        //            }
        //        }
        //    }
        //    return model;
        //}
        #endregion

        public OSModel GetOSByID(int id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="id", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=id,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<OSModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public List<OSModel> GetAllOS()
        {
            return ModelHelper<OSModel>.GetModel_By_DirectSql(sqlGetAllOS, null, oda);
        }

        public int Add(OSModel model)
        {
            return ModelHelper<OSModel>.AddModel_By_QueryParams(model, new List<string>() { "ID" }, oda);
        }

        public void Delete(decimal id)
        {
            //SelfModelHelper<OSModel>.UpdateOrDeleteModel_By_DirectSql(sqlDelete, new List<OracleParameter>(){
            //         new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=id},
            //     }, oda);
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlDelete, new List<OracleParameter>() 
                        { 
                            new OracleParameter("id", OracleDbType.Decimal, 100, id, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
        }

        public int Update(OSModel model)
        {
            return ModelHelper<OSModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=model.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
            }, model, new List<string>() { "ID" }, oda);
        }


        public int AddList(IList<OSModel> models)
        {
            return ModelHelper<OSModel>.AddListModel_By_QueryParams(models, new List<string>() { "ID" }, oda);
        }


        public bool IsExist(string osName)
        {
            var oslist = GetAllOS();
            bool reslut = false;
            string n = null;

            if (oslist.Count > 0)
            {
                foreach (var item in oslist)
                {
                    n = item.OSName.ToLower();
                    if (n == osName.ToLower())
                    {
                        reslut = true;
                        break;
                    }
                }
            }

            return reslut;
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }


    }
}
